using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Security;
using System.Text;
using Wax.Properties;

namespace Wax.Provider
{
    /// <summary>
    /// Implements a data provider that reads and writes test scripts using the excel file format
    /// </summary>
    public class WaxExcelDataProvider : WaxDataProviderBase
    {
        private bool jetNotRegistered;
        private const string Excel2007ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;\";";
        private const string Excel2003ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;\";";


        /// <summary>
        /// Initializes a new instance of the <see cref="WaxExcelDataProvider"/> class.
        /// </summary>
        public WaxExcelDataProvider()
        {
            InitializeFields();
        }

        /// <summary>
        /// Gets a value indicating whether jet is registered on the local machine.
        /// </summary>
        /// <value><c>true</c> if jet is registered on the local machine; otherwise, <c>false</c>.</value>
        public bool JetNotRegistered
        {
            get { return jetNotRegistered; }
        }

        /// <summary>
        /// Parses the file and returns a Collection of <see cref="Test">tests</see>.
        /// </summary>
        /// <param name="fileName">The filename.</param>
        /// <returns></returns>
        public override Collection<Test> ParseFile(string fileName)
        {
            Collection<Test> tests = new Collection<Test>();
            String connectionString = string.Format(CultureInfo.InvariantCulture, GetConnectionString(new FileInfo(fileName)), fileName);
            String query = "Select * from [Sheet1$]";
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();
                using (OleDbCommand command = new OleDbCommand(query, connection))
                {
                    using (OleDbDataReader results = command.ExecuteReader())
                    {
                        if (results.HasRows)
                        {
                            Test test;
                            Dictionary<string, Test> testIdLookup = new Dictionary<string, Test>();

                            int row = 1;
                            while (results.Read())
                            {

                                try
                                {
                                    string testId = GetStringValue(results, 0, string.Empty);
                                    test = GetTest(testId, testIdLookup, tests);

                                    if (test != null)
                                    {
                                        test.Commands.Add(CreateCommandFromResults(results));
                                    }
                                }
                                catch (InvalidFindMethodException findException)
                                {
                                    throw new ParseException(string.Format(CultureInfo.InvariantCulture, Resource.ParseExceptionInvalidFindMethod, findException.InvalidMethodName, row), findException);
                                }

                                row++;
                            }
                        }
                    }
                }
                connection.Close();
            }

            return tests;
        }

        /// <summary>
        /// Gets the connection string based on file type.
        /// </summary>
        /// <param name="fileInfo">The file info.</param>
        /// <returns></returns>
        public string GetConnectionString(FileSystemInfo fileInfo)
        {
            if (fileInfo.Extension == ".xlsx" || JetNotRegistered)
            {
                return string.Format(CultureInfo.InvariantCulture, Excel2007ConnectionString, fileInfo.FullName);
            }
            else
            {
                return string.Format(CultureInfo.InvariantCulture, Excel2003ConnectionString, fileInfo.FullName);
            }
        }

        /// <summary>
        /// Initializes the fields.
        /// </summary>
        private void InitializeFields()
        {
            bool jet4Exists = false;

            try
            {
                using (Microsoft.Win32.RegistryKey key = Microsoft.Win32.Registry.ClassesRoot.OpenSubKey("Microsoft.Jet.OLEDB.4.0"))
                {
                    jet4Exists = key != null;
                }
            }
            catch (SecurityException secExcepiton)
            {
                jet4Exists = false;
                Debug.WriteLine(secExcepiton.Message);
                throw;
            }
            finally
            {
                this.jetNotRegistered = !jet4Exists;
            }

        }

        /// <summary>
        /// Creates the command from results.
        /// </summary>
        /// <param name="results">The results.</param>
        /// <returns>The command</returns>
        private static Command CreateCommandFromResults(OleDbDataReader results)
        {
            string commandId = GetStringValue(results, 1, string.Empty);
            string command = GetStringValue(results, 2, string.Empty);
            string commandArgs = GetStringValue(results, 3, string.Empty);
            string findMethod = GetStringValue(results, 4, string.Empty);
            string findMethodArgs = GetStringValue(results, 5, string.Empty);

            return CommandFactory.Parse(
                new string[]
                    {
                        commandId, command, commandArgs,
                        findMethod, findMethodArgs
                    });
        }

        private static string GetStringValue(IDataRecord results, int index, string defaultValue)
        {
            object value = results.GetValue(index);

            if (value == DBNull.Value)
            {
                value = defaultValue;
            }
            else if (value is double)
            {
                value = Convert.ToString((double)value, CultureInfo.InvariantCulture);
            }

            return (string)value;
        }

        /// <summary>
        /// If the test has already whilst parsing a previous row, returns the existing instance of that test. Otherwise creates
        /// and initializes a new instance of Test.
        /// </summary>
        /// <param name="testId">The current test id</param>
        /// <param name="testIdLookup">Dictionary used to store a lookup between test instances and their id's</param>
        /// <param name="tests">Collection used to store parsed tests.</param>
        /// <returns>An instance of test associated with testId.</returns>
        private static Test GetTest(string testId, IDictionary<string, Test> testIdLookup, ICollection<Test> tests)
        {
            Test test = null;

            if (testIdLookup.ContainsKey(testId))
            {
                test = testIdLookup[testId];
            }
            else
            {
                test = new Test();
                test.Id = testId;
                tests.Add(test);
                testIdLookup.Add(testId, test);
            }

            return test;
        }
    }
}
